﻿CREATE FUNCTION [dbo].[LatePayments](
	@FirstPmtDate datetime,
	@StartDate datetime,
	@Today datetime,
	@ScheduleType char(1)--M,S,B,W
)RETURNS  @T TABLE (PaymentsCount int, LastLateInDays int) --WITH SCHEMABINDING 
AS BEGIN

IF @StartDate = @Today BEGIN	INSERT INTO @T VALUES(1,0) RETURN END
IF @StartDate > @Today OR @StartDate IS NULL OR @Today IS NULL BEGIN	INSERT INTO @T VALUES(0,0) RETURN END

IF @ScheduleType = 'S' BEGIN
DECLARE @PS TABLE(PaymentDate datetime)
INSERT INTO @PS SELECT PaymentDate FROM dbo.NextPaymentDates(@StartDate,@Today,24)
INSERT INTO @T 
SELECT COUNT(*),ISNULL(DATEDIFF(dd,(SELECT TOP 1 PaymentDate FROM @PS ORDER BY PaymentDate DESC),@Today),0) FROM @PS
RETURN
END

IF @ScheduleType IN ('W','B') BEGIN
	DECLARE @W int SET @W = CASE @ScheduleType WHEN 'W' THEN 1 ELSE 2 END
	DECLARE @Weeks int SET @Weeks = DATEDIFF(dd,@StartDate,@Today)/(7*@W)
	DECLARE @LastDate datetime	SELECT @LastDate = DATEADD(ww,@Weeks*@W,@StartDate)
	INSERT INTO @T VALUES(@Weeks+1,DATEDIFF(dd,@LastDate,@Today))
	RETURN
END

DECLARE @Months int SET @Months = dbo.MonthsApart(@StartDate,@Today)
SELECT @LastDate = DATEADD(mm,@Months,@StartDate)

INSERT INTO @T VALUES(@Months+1,DATEDIFF(dd,DATEADD(mm,@Months,@StartDate),@Today))

RETURN
--DECLARE @S datetime,@T datetime SELECT @S = '4/21/08',@T = '1/19/09'
--SELECT * FROM NextPaymentDates(@T,GETDATE(),24)
--SELECT * FROM LatePayments(@S,@T,GETDATE(),'M')
--SELECT * FROM LatePayments(@S,@T,GETDATE(),'S')
--SELECT * FROM LatePayments(@S,@T,GETDATE(),'W')
--SELECT * FROM LatePayments(@S,@T,GETDATE(),'B')
END
